select * from X_SELLING_HOLD_PRODUCT_TAM


select * from tbl_customer_product_holding_this_month 


delete from tbl_customer_product_holding_this_month

insert tbl_customer_product_holding_this_month
SELECT  CONVERT(NVARCHAR(6),BUSINESS_DATE) AS MONTH, BUSINESS_DATE,RECID AS CIF,CUS_NAME,CUS_OPEN_DATE
	,COMPANY_BOOK AS BRANCH_ID,DAO,INDUSTRY_NAME,BRANCH_NAME_SME,REGION,
ISNULL (PRO_AUTO,0) AS PRO_AUTO,
ISNULL (PRO_BF,0) AS PRO_BF,
ISNULL (PRO_BIL,0) AS PRO_BIL,
ISNULL (PRO_CREDIT_PROGRAM,0) AS PRO_CREDIT_PROGRAM,
ISNULL (PRO_FLEX,0) AS PRO_FLEX,
ISNULL (PRO_CLEAN_LOAN,0) AS PRO_CLEAN_LOAN,
ISNULL (PRO_WC,0) AS PRO_WC,
ISNULL (PRO_RICE,0) AS PRO_RICE,
ISNULL (PRO_AQUATIC,0) AS PRO_AQUATIC,
ISNULL (PRO_COFFEE,0) AS PRO_COFFEE,
ISNULL (PRO_WOOD,0) AS PRO_WOOD,
ISNULL (PRO_VUNG4,0) AS PRO_VUNG4,
ISNULL (PRO_NONE,0) AS PRO_NONE,
ISNULL (TGKKH,0) AS TGKKH,
ISNULL(TGCKH,0)TGCKH,
ISNULL(TGCKH_CV,0)TGCKH_CV,
ISNULL(BANCA,0)BANCA,
ISNULL(CARD_DEBIT,0) CARD_DEBIT,
ISNULL(CARD_CREDIT,0)CARD_CREDIT,
ISNULL(FX,0) FX,
ISNULL(POS,0) POS,
ISNULL(UPAS,0) AS UPAS,
ISNULL(LC,0) LC,
ISNULL(NHO_THU,0)NHO_THU,
ISNULL(BAO_LANH_THANH_TOAN,0)BAO_LANH_THANH_TOAN,
ISNULL(BAO_LANH_TH_HOP_DONG,0)BAO_LANH_TH_HOP_DONG,
ISNULL(BAO_LANH_DU_THAU,0)BAO_LANH_DU_THAU,
ISNULL(BAO_LANH_HOA_TAM_UNG,0)BAO_LANH_HOA_TAM_UNG,
ISNULL(BA0_LANH_THANH_TOAN_THUE,0)BAO_LANH_THANH_TOAN_THUE,
ISNULL(BAO_LANH_BAO_HANH,0)BAO_LANH_BAO_HANH,
ISNULL(BAO_LANH_DOI_UNG,0)BAO_LANH_DOI_UNG,
ISNULL(CHIET_KHAU,0)CHIET_KHAU,
ISNULL(TAI_TRO_TRUOC_GIAO_HANG,0)TAI_TRO_TRUOC_GIAO_HANG,
ISNULL(PAY_ROLL,0)PAY_ROLL,
ISNULL(TT_CHUOI,0)TT_CHUOI,
ISNULL(GD_NOIBO,0)GD_NOIBO,
ISNULL(GD_QUOCTE,0)GD_QUOCTE,
ISNULL(THAU_CHI,0) THAU_CHI,
ISNULL(I2B,0)I2B,
ISNULL(TOTAL,0)TOTAL
--INTO tbl_customer_product_holding_this_month
FROM ProductHolding.dbo.TBL_VPB_CUSTOMER_20170228

DELETE FROM CUST_USED_PRODUCT_HOLDING_THIS_MONTH

INSERT CUST_USED_PRODUCT_HOLDING_THIS_MONTH
SELECT * 
FROM 
   (SELECT  MONTH, CIF,[PRO_AUTO],[PRO_BF],[PRO_BIL],[PRO_CREDIT_PROGRAM],[PRO_FLEX],[PRO_CLEAN_LOAN],[PRO_WC],[PRO_RICE],[PRO_AQUATIC],[PRO_COFFEE],[PRO_WOOD],[PRO_VUNG4],[PRO_NONE],[TGKKH],[TGCKH],[TGCKH_CV],[BANCA],[CARD_DEBIT],[CARD_CREDIT],[FX],[POS],[UPAS],[LC],[NHO_THU],[BAO_LANH_THANH_TOAN],[BAO_LANH_TH_HOP_DONG],[BAO_LANH_DU_THAU],[BAO_LANH_HOA_TAM_UNG],[BAO_LANH_THANH_TOAN_THUE],[BAO_LANH_BAO_HANH],[BAO_LANH_DOI_UNG],[CHIET_KHAU],[TAI_TRO_TRUOC_GIAO_HANG],[PAY_ROLL],[TT_CHUOI],[GD_NOIBO],[GD_QUOCTE],[THAU_CHI],[I2B] 
   FROM tbl_customer_product_holding_this_month) P
UNPIVOT
   (HAVE_PRODUCT FOR PRODUCT IN 
      ([PRO_AUTO],[PRO_BF],[PRO_BIL],[PRO_CREDIT_PROGRAM],[PRO_FLEX],[PRO_CLEAN_LOAN],[PRO_WC],[PRO_RICE],[PRO_AQUATIC],[PRO_COFFEE],[PRO_WOOD],[PRO_VUNG4],[PRO_NONE],[TGKKH],[TGCKH],[TGCKH_CV],[BANCA],[CARD_DEBIT],[CARD_CREDIT],[FX],[POS],[UPAS],[LC],[NHO_THU],[BAO_LANH_THANH_TOAN],[BAO_LANH_TH_HOP_DONG],[BAO_LANH_DU_THAU],[BAO_LANH_HOA_TAM_UNG],[BAO_LANH_THANH_TOAN_THUE],[BAO_LANH_BAO_HANH],[BAO_LANH_DOI_UNG],[CHIET_KHAU],[TAI_TRO_TRUOC_GIAO_HANG],[PAY_ROLL],[TT_CHUOI],[GD_NOIBO],[GD_QUOCTE],[THAU_CHI],[I2B] )
)AS UNPVT;

--update X_SELLING_HOLD_PRODUCT_TAM
update a
set a.YEARMONTH=b.MONTH
from X_SELLING_HOLD_PRODUCT_TAM a, CUST_USED_PRODUCT_HOLDING_THIS_MONTH b
where b.HAVE_PRODUCT = 1 and a.PRODUCT = b.PRODUCT and a.CIF = b.CIF 

insert X_SELLING_HOLD_PRODUCT_TAM
select MONTH,CIF, PRODUCT from CUST_USED_PRODUCT_HOLDING_THIS_MONTH 
where HAVE_PRODUCT = 1 and CIF not in (select cif from X_SELLING_HOLD_PRODUCT_TAM)

select * from X_SELLING_HOLD_PRODUCT_TAM order by YEARMONTH 

select * from CUST_USED_PRODUCT_IN_PAST

delete from CUST_USED_PRODUCT_IN_PAST

INSERT CUST_USED_PRODUCT_IN_PAST 
SELECT A.YEARMONTH, A.CIF, A.PRODUCT, B.CUS_NAME, B.CUS_OPEN_DATE, B.BRANCH_ID, C.BRANCH_NAME_SME, C.REGION, B.BUSINESS_STATUS,B.CUS_STATUS,B.INDUSTRY_NAME,B.INDUSTRY_NAME_EN, b.DAO, b.dao_name
FROM X_SELLING_HOLD_PRODUCT_TAM A, TBL_CUSTOMER B, TBL_BRANCH C,CUST_USED_PRODUCT_HOLDING_THIS_MONTH D
WHERE A.CIF=D.CIF AND A.CIF = B.CIF AND A.PRODUCT = D.PRODUCT AND B.BRANCH_ID =C.BRANCH_ID  and d.HAVE_PRODUCT = 0


--INSERT TBL_DATE_BUSINESS
--select MAX_DATE_ENDMONTH, 'CUST_USED_PRODUCT_IN_PAST' from DATE_EM_REPORT

update b
set b.date_data = (select MAX_DATE_ENDMONTH from DATE_EM_REPORT)
from TBL_DATE_BUSINESS b
where b.name_table = 'CUST_USED_PRODUCT_IN_PAST'

delete from server12.SMECustomer360.[dbo].CUST_USED_PRODUCT_IN_PAST
insert server12.SMECustomer360.dbo.CUST_USED_PRODUCT_IN_PAST
select * from CUST_USED_PRODUCT_IN_PAST